FormulaArray

FormulaArray (Range.FormulaArray property), bir Range nesnesinin dizi formülünü okumanızı ya da yazmanızı sağlayan özelliktir. Elle Ctrl+Shift+Enter ile girilen, süslü parantezli klasik dizi formülünün VBA karşılığıdır. Tek hücreye ya da çok hücreli bir aralığa tek bir dizi formülü yazarak, birden çok değeri aynı anda işleyen hesapları makroyla kurmanızı sağlar.

Söz dizimi​

Özelliğe yazarken formülü metin olarak verirsiniz; okurken de formül metin olarak döner.

• Aralık: Zorunlu. Bir Range nesnesidir; tek hücre ya da çok hücreli aralık olabilir. Çok hücreli aralıkta formül aralığın tamamına tek bir dizi formülü olarak yazılır.
• formül: Zorunlu (yazarken). Eşittir işaretiyle başlayan bir metindir. A1 başvuru stilinde, İngilizce işlev adlarıyla ve virgül ayırıcıyla yazılmalıdır. Metin en çok 255 karakter olabilir.

Özellik Formula gibi Türkçe işlev adlarını ya da noktalı virgül ayırıcıyı kabul etmez. Örneğin Türkçe Excel kullanıyor olsanız bile TOPLA yerine SUM yazmanız gerekir.

Dönüş değeri ve okuma​

Özellik Variant türünde okunur ve yazılır. Dizi formülü içeren bir hücrede okuduğunuzda formül metni, Excel'in gösterdiği süslü parantezler olmadan döner. Bir hücrenin dizi formülünün parçası olup olmadığını HasArray özelliğiyle, dizi formülünün kapladığı tüm aralığı da CurrentArray özelliğiyle öğrenebilirsiniz.
Özelliğe yazdığınız formül hücrelere eşdeğer bir klasik dizi formülü olarak girilir. Excel'in elle Ctrl+Shift+Enter ile oluşturduğu sonucun aynısını elde edersiniz.

Dizi formülünün çalışma biçimi ve sınırları​

Dizi formülü, işlevlere tek değer yerine değer aralıkları verip işlemi her eleman için yapar. Örneğin bir şehre ait satışları toplayan formülün Türkçe ve İngilizce karşılığı şöyledir:
VBA'da bu formülü FormulaArray özelliğine İngilizce ve virgüllü biçimiyle yazarsınız. Önemli sınırlar şunlardır:

• 255 karakter sınırı: Formül metni bu sınırı aşarsa çalışma zamanı hatası (1004) oluşur. Uzun formülleri kısaltmak için tanımlı adlar kullanın ya da formülü parçalara bölüp kısa bir yer tutucuyu Replace işleviyle sonradan değiştirin.
• Kısmi değişiklik yapılamaz: Çok hücreli bir dizi formülünün yalnızca bir hücresini değiştiremez, silemezsiniz. Önce CurrentArray ile tüm aralığa ulaşıp ClearContents ile temizleyin.
• Boyut uyumu: Sonucun boyutu yazdığınız aralıktan küçükse fazla hücrelerde #YOK hatası görünür.

FormulaArray, Formula ve Formula2 arasındaki fark​

• Formula: Dizi olmayan, tek hücreli normal formül yazar. Microsoft 365 öncesi sürümlerde örtülü kesişim uygular.
• Formula2: Microsoft 365 ve Excel 2021 sonrasındaki dinamik dizi davranışıyla formül yazar. Sonuç taşıyorsa komşu hücrelere kendiliğinden yayılır ve süslü parantez görünmez.
• FormulaArray: Her zaman klasik, sabit boyutlu ve süslü parantezli dizi formülü yazar. Eski sürümlerle uyumlu çalışma kitapları için tercih edilir.

Yeni sürümlerde sonucun kendiliğinden yayılmasını istiyorsanız Formula2, eski sürümlerle uyumluluk ya da sabit dizi aralığı gerekiyorsa FormulaArray kullanın.

1. Tek hücrede dizi formülüyle toplam ciro​

Adet ve birim fiyat sütunlarından toplam ciroyu tek hücrede hesaplamak istiyorsunuz. Satır satır tutar sütunu açmadan, adetle fiyatı çarpıp toplayan bir dizi formülünü D2 hücresine yazıyoruz. Kodu standart bir modüle yapıştırın.
Makro çalışınca D2 hücresinde 2.829,50 değeri görünür. Formül çubuğunda formül süslü parantezler içinde gösterilir; bu, hücrenin klasik dizi formülü olduğunu belirtir. Aynı hesabı normal bir formülle yapmak için Formula yerine TOPLA.ÇARPIM işlevini kullanabilirsiniz, ama dizi çarpımı örneği FormulaArray'in mantığını açıkça gösterir.

2. Çok hücreli aralığa tek dizi formülü yazmak​

Her satırın tutarını (adet çarpı birim fiyat) tek bir dizi formülüyle D2:D7 aralığına yazıyoruz. Formül aralığın tamamına aittir; hücreler tek tek değil, birlikte hesaplanır. Kodu standart bir modüle yapıştırın.
Makro çalışınca ilk satırın tutarı D2 hücresinde 1.945,00, son satırın tutarı D7 hücresinde 5.780,00 olarak görünür. Altı hücre tek bir dizi formülüne bağlıdır; bu nedenle D4 gibi tek bir hücreyi silmeye ya da değiştirmeye çalışırsanız Excel izin vermez. Değişiklik için önce tüm D2:D7 aralığını seçip silmeniz ya da kodda CurrentArray.ClearContents kullanmanız gerekir.

3. Koşullu toplam ve HasArray ile denetim​

C2 hücresine yazdığınız şehre ait satışları toplayan bir dizi formülünü, formül metnini birleştirerek oluşturuyoruz. Ardından HasArray özelliğiyle D2 hücresinin gerçekten dizi formülü içerdiğini E2 hücresine yazıyoruz. Kodu standart bir modüle yapıştırın.
C2 hücresindeki şehir için D2 hücresinde toplam satış 28.600 olarak hesaplanır. E2 hücresindeki DOĞRU değeri, D2 hücresinin dizi formülü içerdiğini gösterir. Şehir adını C2 hücresinde değiştirip makroyu yeniden çalıştırırsanız formül yeni şehre göre yeniden yazılır. Formül metni değişkende birleştirildiği için 255 karakter sınırını aşmadığından emin olun.

Kullanırken dikkat edin​

• 255 karakter sınırı: Formül metni 255 karakteri aşarsa çalışma zamanı hatası alırsınız. Uzun formüllerde tanımlı ad kullanın, formülü iç içe geçmiş kısa parçalara bölün ya da Microsoft 365'te Formula2 özelliğini deneyin.
• Dil ve ayırıcı: FormulaArray her zaman İngilizce işlev adları, virgül ayırıcı ve A1 başvuru stili bekler. Türkçe işlev adı ya da noktalı virgül yazarsanız hata oluşur. Türkçe adlarla çalışmak isterseniz FormulaLocal özelliği normal formüller için kullanılabilir, ama dizi formülü için güvenilir bir karşılığı yoktur.
• Kısmi değişiklik hatası: Çok hücreli bir dizi formülünün parçası olan hücreye Value, Formula ya da ClearContents ile yazmaya çalışırsanız "Dizi formülünün bir bölümü değiştirilemez" hatası alırsınız. Önce CurrentArray ile tüm aralığı alın, sonra bütün olarak silin ya da yeniden yazın.
• Aralık boyutu: Çok hücreli aralığa yazarken sonucun boyutunu hesaba katın. Aralık sonuçtan büyükse artan hücrelerde #YOK hatası görünür; küçükse sonuç kırpılır. Formülün tek bir toplam döndürdüğü durumlarda tek hücreli aralık kullanın.
• Dizi formülü yavaşlığı: Büyük veri aralıklarında çok sayıda dizi formülü hesaplamayı yavaşlatır. Mümkünse tüm sütun başvuruları yerine gerçek veri aralığını kullanın ve hesaplama için gereksiz yere yeniden yazma yapmayın.
• İlgili konular:
  • Formula: Normal formül yazan özellik.
  • Formula2: Dinamik dizi formülü yazan özellik.
  • FormulaR1C1: R1C1 başvuru stiliyle formül yazan özellik.
  • HasArray ve CurrentArray: Dizi formülünü denetleme ve tüm aralığına ulaşma.
  • ClearContents: Aralığın içeriğini temizleyen yöntem.
  • TOPLA, EĞER, TOPLA.ÇARPIM, DEVRİK_DÖNÜŞÜM: Dizi formüllerinde sık kullanılan işlevler.

Örneklerdeki makroların tamamı indirilebilir Excel dosyasındadır (.xlsm). Dosyayı açtıktan sonra makroları etkinleştirin, ardından Alt + F11 ile Visual Basic Düzenleyicisi'nde kodları inceleyebilir ve Alt + F8 ile çalıştırabilirsiniz.